<?php

namespace App\Console\Commands;

/*use App\Models\Company;
use App\Models\Member;
use App\Models\Order;
use App\Models\OrderList;*/
use Carbon\Carbon;
use Illuminate\Console\Command;
use Illuminate\Database\Eloquent\ModelNotFoundException;
use Illuminate\Support\Facades\Log;
use DB;

class CalculateTotal extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'calculate_total';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'calculate_total';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $end_time = Carbon::yesterday()->subDays(1)->endOfDay()->timestamp;
        try {
            $companyArr = collect([]);
            DB::transaction(function() use($end_time,$companyArr){
                //计算数据先清空数据库
                DB::table('statistics_customer_day')->truncate();
                DB::table('statistics_customer_month')->truncate();
                DB::table('statistics_customer_year')->truncate();
                DB::table('statistics_goods')->truncate();
                //然后查询所有的客户
                //$companies = Company::select('id','company_name','amount','order_volume','user_number')->get();
                $companies = DB::table('company')->select('id','company_name','amount','order_volume','user_number','order_money','cost')->get();
                //查询所有已发货订单
                //$orders = Order::withoutGlobalScopes()->select('id','send_time','send_money','company_id')->whereIn('order_status',[2,3])->where('send_time','<=',$end_time)->get();
                $orders = DB::table('order')->select('id','send_time','send_money','order_money','company_id')
                    ->selectRaw('(SELECT SUM(ROUND(inventory_price*sort_num,2)) as cost FROM '.config('database.connections.mysql.prefix').'order_list WHERE '.config('database.connections.mysql.prefix').'order_list.oid='.config('database.connections.mysql.prefix').'order.id) as cost')
                    ->whereIn('order_status',[2,3])->where('send_time','<=',$end_time)->get();
                $this->bar =$this->output->createProgressBar($companies->count());
                $orders_for_company = $orders->groupBy('company_id');
                //每个客户单独处理
                foreach ($companies as $company) {
                    if (!isset($orders_for_company[$company->id])) continue;
                    $companySingle = $this->calculateAmount($orders_for_company[$company->id],$company);
                    $companyArr->push($companySingle);
                    $this->bar->advance();
                }
                $order_list = DB::table('order_list')->selectRaw("standard_goods_id as goods_id,standard_spu_id as goods_spu_id,SUM(sort_num) as sales,SUM(ROUND(sort_num*sort_price,2)) as amount")
                    ->where('standard_spu_id','>',0)->groupBy('standard_spu_id')->get()->toJson();
                //Log::info(json_decode($order_list,true));
                $order_list = json_decode($order_list,true);
                /*$order_list = DB::table('order_list')->select('id','standard_goods_id','standard_spu_id','sort_num')
                //$order_list = OrderList::withoutGlobalScopes()->select('id','standard_goods_id','standard_spu_id','sort_num')
                    ->selectRaw("ROUND(sort_num*sort_price,2) as sort_money")
                    ->where('standard_goods_id','>',0)->where('standard_spu_id','>',0)
                    ->get()->groupBy('standard_spu_id')->transform(function ($item,$k){
                        $data = [
                            'goods_id' => $item->first()->standard_goods_id,
                            'goods_spu_id' => $k,
                            //'amount' => decimal2places($item->sum('sort_money')),
                            'amount' => round($item->sum('sort_money'),2),
                            //'sales' => decimal2places($item->sum('sort_num')),
                            'sales' => round($item->sum('sort_num'),2)
                        ];
                        return $data;
                    })->toArray();*/
                //Log::info($order_list);
                //dd($order_list);
                DB::table('statistics_goods')->insert($order_list);

                $this->bar->finish();
                $companyArr =  $companyArr->toArray();
                $headers = ['company_name', 'amount','order_volume','user_number','order_money','cost'];
                return $this->table($headers, $companyArr);
            },5);
        } catch (\Exception $e) {
            Log::info('calculate_total:company error',['msg'=>$e->getMessage(),'trace'=>$e->getTrace()]);
            return $this->error("服务器内部错误！");
        }
    }

    protected function calculateAmount($orders,$company)
    {
        $amount = $orders->sum('send_money');
        $order_volume = $orders->count();
        $order_money = round($orders->sum('order_money'),2);
        $cost = round($orders->sum('cost'),2);
        //$user_number = Member::withoutGlobalScopes()->select('id')->where('status',1)->where('is_del',0)->where('company_id',$company->id)->count();
        $user_number = DB::table('member')->select('id')->where('status',1)->where('is_del',0)->where('company_id',$company->id)->count();
        $months = $years = [];
        $days = $orders->groupBy('send_time')->transform(function ($item,$key)use ($user_number,$company,&$months,&$years){
            //$amount = decimal2places($item->sum('send_money'));
            $amount = round($item->sum('send_money'),2);
            $order_money = round($item->sum('order_money'),2);
            $cost = round($item->sum('cost'),2);
            $order_volume = $item->count();
            $data = [
                'dates' => date("Y-m-d",$key),
                'timestamps' => $key,
                'amount' => $amount,
                'order_volume' => $order_volume,
                'user_number' => $user_number,
                'company_id' => $company->id,
                'order_money' => $order_money,
                'cost' => $cost
            ];
            $month = date("Y-m",$key);
            if (isset($months[$month])) {
                $months[$month]['amount'] += $amount;
                $months[$month]['order_volume'] += $order_volume;
                $months[$month]['order_money'] += $order_money;
                $months[$month]['cost'] += $cost;
            } else {
                $months[$month] = [
                    'dates' => $month,
                    'timestamps' => strtotime($month.'-01'),
                    'amount' => $amount,
                    'order_volume' => $order_volume,
                    'user_number' => $user_number,
                    'company_id' => $company->id,
                    'order_money' => $order_money,
                    'cost' => $cost
                ];
            }
            $year = date("Y",$key);
            if (isset($years[$year])) {
                $years[$year]['amount'] += $amount;
                $years[$year]['order_volume'] += $order_volume;
                $years[$year]['order_money'] += $order_money;
                $years[$year]['cost'] += $cost;
            } else {
                $years[$year] = [
                    'dates' => $year,
                    'timestamps' => strtotime($year.'-01-01'),
                    'amount' => $amount,
                    'order_volume' => $order_volume,
                    'user_number' => $user_number,
                    'company_id' => $company->id,
                    'order_money' => $order_money,
                    'cost' => $cost
                ];
            }
            return $data;
        })->toArray();

        DB::table('statistics_customer_day')->insert($days);
        DB::table('statistics_customer_month')->insert($months);
        DB::table('statistics_customer_year')->insert($years);

        //$company->amount = $amount;
        //$company->order_volume = $order_volume;
        //$company->user_number = $user_number;
        //$company->save();

        DB::table('company')->where('id',$company->id)->update([
            'amount' => $amount,
            'order_volume' => $order_volume,
            'user_number' => $user_number,
            'order_money' => $order_money,
            'cost' => $cost
        ]);

        $company_name = $company->company_name;
        return compact('company_name','amount','order_volume','user_number','order_money','cost');
    }
}
